//
//  DbManager.m
//  AppFinance
//
//  Created by wei on 12-6-11.
//  Copyright (c) 2012年 __MyCompanyName__. All rights reserved.
//

#import "AFDbManager.h"
//#import "newsPaperData.h"
@implementation AFDbManager


static AFDbManager *_dbManager = nil;

+ (AFDbManager*) dbManager{
    @synchronized(self){
        if (_dbManager == nil) {
            _dbManager = [[self alloc] init];
            [_dbManager initDb];
        }
    }
    return _dbManager;
}

- (void) initDb {
    [self openDb];
    [self createTable];
}

- (void) openDb {
    //paths： ios下Document路径，Document为ios中可读写的文件夹
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentDirectory = [paths objectAtIndex:0];
    NSLog(@"documentDirectory = %@",documentDirectory);
    //dbPath： 数据库路径，在Document中。
    NSString *dbPath = [documentDirectory stringByAppendingPathComponent:@"HTAFV301.db"]; // HT.db
    
    //创建数据库实例 db  这里说明下:如果路径中不存在"Ht.db"的文件,sqlite会自动创建"Ht.db"
    db = [FMDatabase databaseWithPath:dbPath];
    if (![db open]) {
        NSLog(@"Could not open db.");
        return ;
    }
    
    NSLog(@" --- open db success ---");
}

-(void) createTable{
    NSInteger count = 0;
    // 我的自选
    FMResultSet *rs1 = [db executeQuery:@"SELECT COUNT(*) AS 'count' FROM sqlite_master WHERE type = 'table' AND name = ?;",@"UserCustom"];
    while ([rs1 next]) {
        count = [rs1 intForColumn:@"count"];
    }
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS UserCustom (excode text, code text)"];
    
    if (count == 0) {
        //给自选，创建默认的数据
        [self insertDataWithExCode:@"WH" code:@"USDJPY"];
        [self insertDataWithExCode:@"WH" code:@"GBPUSD"];
        [self insertDataWithExCode:@"WH" code:@"AUDUSD"];
        [self insertDataWithExCode:@"WH" code:@"EURUSD"];
        [self insertDataWithExCode:@"WH" code:@"USD"];
        [self insertDataWithExCode:@"NYMEX" code:@"CONC"];
        [self insertDataWithExCode:@"WGJS" code:@"XAG"];
        [self insertDataWithExCode:@"WGJS" code:@"XAU"];


        NSLog(@"我的自选 初始化 添加默认数据成功！");
    }else{
        [db executeUpdate:@"DELETE FROM UserCustom WHERE excode IN ('SZPEC','YRDCE','HFCE','HXCE')"];
    }
    
    
    
    // 邮币卡 自选
    count = 0;
    FMResultSet *rs = [db executeQuery:@"SELECT COUNT(*) AS 'count' FROM sqlite_master WHERE type = 'table' AND name = ?;",@"YouBikaUserCustom"];
    while ([rs next]) {
        count = [rs intForColumn:@"count"];
    }

    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS YouBikaUserCustom (excode text, code text)"];
    
    if (count == 0) {
        //给自选，创建默认的数据
        [db executeUpdate:@"INSERT INTO YouBikaUserCustom (excode, code) VALUES('HBCPRE','HBC000001'),('HUAXIACAE','HUA000001'),('WJYBK','WJY100001'),('JSCAEE','JSC100001'),('CNSCEE','CNS000001'),('JINMAJIA','JIN000100'),('ZNYPJY','ZNY100001'),('NJSCAE','NJS100001');"];
        
        NSLog(@"邮币卡-自选 初始化 添加默认数据成功！");
    }

    
    // 新闻资讯 收藏
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS NewsCollecting (ncolumn text, nid text, title text, time text, imageurl text)"];
    [db executeUpdate:@"DELETE FROM NewsCollecting WHERE ncolumn = ?",@"NEWS_XI_DU_SHI_YOU"];
    
    // 大宗行业 新闻收藏
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS OTCBetaNewsCollecting (ncolumn text, nid text, title text, time text, imageurl text)"];
    
    //  报刊杂志 新闻收藏
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS PaperNewsCollecting (ncolumn text, nid text, oid text, pid text, title text, time text, imageurl text)"];
    
    // 报刊杂志 订阅
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS PAPERreading (ncolumn text, oid text, pid text)"];
    
//    count = 0 ;
//    FMResultSet *rs2 = [db executeQuery:@"SELECT COUNT(*) AS 'count' FROM sqlite_master WHERE type = 'table' AND name = ?;",@"PAPERreading"];
//    while ([rs2 next]) {
//        count = [rs2 intForColumn:@"count"];
//    }
//
//    
//    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS PAPERreading (ncolumn text, oid text, pid text)"];
//    if (count == 0) {
//
//        [db executeUpdate:@"INSERT INTO PAPERreading (oid, pid) VALUES ('1','5'),('1','6'),('1','9');"];
//        [[newsPaperData manager]paperSubscribeWithPid:@"5" type:@"1" theDefault:YES];
//        [[newsPaperData manager]paperSubscribeWithPid:@"6" type:@"1" theDefault:YES];
//        [[newsPaperData manager]paperSubscribeWithPid:@"9" type:@"1" theDefault:YES];
//       
//    }
    
    // 广告配置
    [db executeUpdate:@"CREATE TABLE IF NOT EXISTS HTAdvert304 (kind text, key text, title text, url text, type text, identify text, imageurl text, time text,v text)"];
    
    NSLog(@" --- CREATE Table success ---");
}

////////////////////////////////////////////////////////////////////////////////
#pragma mark - Table UserCustom
////////////////////////////////////////////////////////////////////////////////
- (BOOL) insertDataWithExCode:(NSString*)_exCode code:(NSString*)_code {
    BOOL result = [db executeUpdate:@"INSERT INTO UserCustom (excode, code) VALUES (?,?)",_exCode,_code];
    NSLog(@" --- insertData success ---");
    return result;
}

- (BOOL) deleteDataWithCode:(NSString*)_code {
    BOOL result = [db executeUpdate:@"DELETE FROM UserCustom WHERE code = ?",_code];
    NSLog(@" --- deleteData success ---");
    return result;
}

- (void) deleteAllData{
    [db executeUpdate:@"DELETE FROM UserCustom"];
    NSLog(@" --- deleteAllData success ---");
}

- (NSString*) selectKindKeyWithCode:(NSString*)_code {
    
    NSString *kind_key = @"";

    FMResultSet *rs = [db executeQuery:@"SELECT * FROM UserCustom WHERE code = ?",_code];
    while ([rs next]){
        kind_key = [rs stringForColumn:@"KindKey"];
    }
    [rs close];
    
    return kind_key;
}

- (BOOL) isExistCode:(NSString*)_code {
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM UserCustom WHERE code = ?",_code];
    if ([rs next]) {
        return true;
    }
    return false;
}

- (NSMutableArray*) selectAllData {
    NSMutableArray *_array = [NSMutableArray array];
    NSMutableDictionary *_dict = nil;

    //返回全部查询结果
    FMResultSet *rs=[db executeQuery:@"SELECT * FROM UserCustom"];
    while ([rs next]){
        NSLog(@" Excode = %@, Code = %@",[rs stringForColumn:@"excode"],[rs stringForColumn:@"code"]);
        
        _dict = [NSMutableDictionary dictionary];
        [_dict setValue:[rs stringForColumn:@"excode"] forKey:@"excode"];
        [_dict setValue:[rs stringForColumn:@"code"] forKey:@"code"];
        
        [_array addObject:_dict];
    }
    [rs close];
    return _array;
}

- (int) userSelfTableCount{
    int count = 0;
    
    //返回全部查询结果
    FMResultSet *rs = [db executeQuery:@"SELECT COUNT(*) FROM UserCustom"];
    while ([rs next]){
        count = [rs intForColumnIndex:0];
    }
    [rs close];
    return count;
}


/////////////////////////////////////////////////////////////////////////
#pragma mark - Table BikaSelf
/////////////////////////////////////////////////////////////////////////
-(BOOL) insertBikaDataWithExCode:(NSString*)_exCode code:(NSString*)_code{
   BOOL result = [db executeUpdate:@"INSERT INTO YouBikaUserCustom (excode, code) VALUES (?,?)",_exCode,_code];
    NSLog(@" --- insertBikaData success ---");
    return result;
}
-(BOOL) deleteBikaDataWithCode:(NSString*)_code{
   BOOL result = [db executeUpdate:@"DELETE FROM YouBikaUserCustom WHERE code = ?",_code];
    NSLog(@" --- deleteBiakData success ---");
    return result;
}
-(void) deleteAllBikaData{
    [db executeUpdate:@"DELETE FROM YouBikaUserCustom"];
    NSLog(@" --- deleteAllBIkaData success ---");
}

-(NSString*) selectBikaKindKeyWithCode:(NSString*)_code{
    NSString *kind_key = @"";
    
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM YouBikaUserCustom WHERE code = ?",_code];
    while ([rs next]){
        kind_key = [rs stringForColumn:@"KindKey"];
    }
    [rs close];
    
    return kind_key;
}

-(BOOL) isExistBikaCode:(NSString*)_code{
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM YouBikaUserCustom WHERE code = ?",_code];
    if ([rs next]) {
        return true;
    }
    return false;
}
-(NSMutableArray*) selectAllBikaData{
    NSMutableArray *_array = [NSMutableArray array];
    NSMutableDictionary *_dict = nil;
    
    //返回全部查询结果
    FMResultSet *rs=[db executeQuery:@"SELECT * FROM YouBikaUserCustom"];
    while ([rs next]){
        NSLog(@" Excode = %@, Code = %@",[rs stringForColumn:@"excode"],[rs stringForColumn:@"code"]);
        
        _dict = [NSMutableDictionary dictionary];
        [_dict setValue:[rs stringForColumn:@"excode"] forKey:@"excode"];
        [_dict setValue:[rs stringForColumn:@"code"] forKey:@"code"];
        
        [_array addObject:_dict];
    }
    [rs close];
    return _array;
    
}
-(int) BikaSelfTableCount{
    int count = 0;
    
    //返回全部查询结果
    FMResultSet *rs = [db executeQuery:@"SELECT COUNT(*) FROM YouBikaUserCustom"];
    while ([rs next]){
        count = [rs intForColumnIndex:0];
    }
    [rs close];
    return count;
}

////////////////////////////////////////////////////////////////////////////////
#pragma mark - Table NewsCollecting
////////////////////////////////////////////////////////////////////////////////
//[db executeUpdate:@"CREATE TABLE NewsCollecting (ncolumn text, nid text, title text, time text, imageurl text)"];

- (BOOL) newsCollectionInsertDataWithColumn:(NSString*)_column newsId:(NSString*)_newsId title:(NSString*)_title
                                       time:(NSString*)_time imageUrl:(NSString *)_url{
    BOOL result = [db executeUpdate:@"INSERT INTO NewsCollecting (ncolumn, nid, title, time, imageurl) VALUES (?,?,?,?,?)",_column, _newsId, _title, _time, _url];
    return result;
}

- (BOOL) newsCollectionDeleteDataWithId:(NSString*)_id {
    BOOL result = [db executeUpdate:@"DELETE FROM NewsCollecting WHERE nid = ?", _id];
    return  result;
}

- (void) newsCollectionDeleteAllData {
    [db executeUpdate:@"DELETE FROM NewsCollecting"];
}

- (BOOL) newsCollectionIsExistCode:(NSString*)_id {
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM NewsCollecting WHERE nid = ?",_id];
    if ([rs next]) {
        return true;
    }
    return false;
}

- (NSMutableArray*) newsCollectioSelectAll{
    NSMutableArray *_array = [NSMutableArray array];
    NSMutableDictionary *_dict = nil;
    
    //返回全部查询结果
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM NewsCollecting"];
    while ([rs next]){
        //NSLog(@" KindKey = %@, Code = %@",[rs stringForColumn:@"KindKey"],[rs stringForColumn:@"Code"]);
        
        _dict = [NSMutableDictionary dictionary];
        [_dict setValue:[rs stringForColumn:@"ncolumn"] forKey:@"NewsColumn"];
        [_dict setValue:[rs stringForColumn:@"nid"] forKey:@"nid"];
        [_dict setValue:[rs stringForColumn:@"title"] forKey:@"title"];
        [_dict setValue:[rs stringForColumn:@"time"] forKey:@"publish"];
        [_dict setValue:[rs stringForColumn:@"imageurl"] forKey:@"picture"];
       
        [_array addObject:_dict];
    }
    [rs close];
    return _array;
}


////////////////////////////////////////////////////////////////////////////////
#pragma mark - OTCBeta NewsCollecting
////////////////////////////////////////////////////////////////////////////////
//[db executeUpdate:@"CREATE TABLE NewsCollecting (ncolumn text, nid text, title text, time text, imageurl text)"];

- (BOOL) otcbetaNewsCollectionInsertDataWithColumn:(NSString*)_column newsId:(NSString*)_newsId title:(NSString*)_title
                                       time:(NSString*)_time imageUrl:(NSString *)_url {
    BOOL result = [db executeUpdate:@"INSERT INTO OTCBetaNewsCollecting (ncolumn, nid, title, time, imageurl) VALUES (?,?,?,?,?)",_column, _newsId, _title, _time, _url];
    return  result;
}

- (BOOL) otcbetaNewsCollectionDeleteDataWithId:(NSString*)_id {
    BOOL result = [db executeUpdate:@"DELETE FROM OTCBetaNewsCollecting WHERE nid = ?", _id];
    return  result;
}

- (void) otcbetaNewsCollectionDeleteAllData {
    [db executeUpdate:@"DELETE FROM OTCBetaNewsCollecting"];
}

- (BOOL) otcbetaNewsCollectionIsExistCode:(NSString*)_id {
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM OTCBetaNewsCollecting WHERE nid = ?",_id];
    if ([rs next]) {
        return true;
    }
    return false;
}

- (NSMutableArray*) otcbetaNewsCollectioSelectAll{
    NSMutableArray *_array = [NSMutableArray array];
    NSMutableDictionary *_dict = nil;
    
    //返回全部查询结果
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM OTCBetaNewsCollecting"];
    while ([rs next]){
        //NSLog(@" KindKey = %@, Code = %@",[rs stringForColumn:@"KindKey"],[rs stringForColumn:@"Code"]);
        
        _dict = [NSMutableDictionary dictionary];
        [_dict setValue:[rs stringForColumn:@"ncolumn"] forKey:@"OTCBetaNewsColumn"];
        [_dict setValue:[rs stringForColumn:@"nid"] forKey:@"nid"];
        [_dict setValue:[rs stringForColumn:@"title"] forKey:@"title"];
        [_dict setValue:[rs stringForColumn:@"time"] forKey:@"publish"];
        [_dict setValue:[rs stringForColumn:@"imageurl"] forKey:@"picture"];
        
        [_array addObject:_dict];
    }
    [rs close];
    return _array;
}

//////////////////////////////////////////////////////////////////////////
#pragma mark - Table NewsPaperCollection
//////////////////////////////////////////////////////////////////////////

- (BOOL) newsPaperCollectionInsertDataWithColumn:(NSString*)_column newsId:(NSString*)_newsId newsOid:(NSString *)_oid newsPid:(NSString *)_pid title:(NSString*)_title  time:(NSString*)_time imageUrl:(NSString *)_url{
    
    BOOL result = [db executeUpdate:@"INSERT INTO PaperNewsCollecting (ncolumn, nid, oid, pid ,title, time, imageurl) VALUES (?,?,?,?,?,?,?)",_column, _newsId, _oid,_pid, _title, _time, _url];
    return  result;
    
}

-(BOOL) newsPaperCollectionDeleteDataWithId:(NSString*)_id oid:(NSString *)_oid{
    BOOL result = [db executeUpdate:@"DELETE FROM PaperNewsCollecting WHERE nid = ? and oid = ?", _id , _oid];
    return  result;
}

-(void) newsPaperCollectionDeleteAllData{
    [db executeUpdate:@"DELETE FROM PaperNewsCollecting"];

}
-(BOOL) newsPaperCollectionIsExistCode:(NSString*)_id oid:(NSString *)_oid{
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM PaperNewsCollecting WHERE nid = ? and oid = ?",_id, _oid];
    if ([rs next]) {
        return true;
    }
    return false;

}
-(NSMutableArray*) newsPaperCollectioSelectAll{
    NSMutableArray *_array = [NSMutableArray array];
    NSMutableDictionary *_dict = nil;
    
    //返回全部查询结果
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM PaperNewsCollecting"];
    while ([rs next]){
        //NSLog(@" KindKey = %@, Code = %@",[rs stringForColumn:@"KindKey"],[rs stringForColumn:@"Code"]);
        
        _dict = [NSMutableDictionary dictionary];
        [_dict setValue:[rs stringForColumn:@"ncolumn"] forKey:@"newsPaperColumn"];
        [_dict setValue:[rs stringForColumn:@"nid"] forKey:@"nid"];
        [_dict setValue:[rs stringForColumn:@"oid"] forKey:@"oid"];
         [_dict setValue:[rs stringForColumn:@"pid"] forKey:@"pid"];
        [_dict setValue:[rs stringForColumn:@"title"] forKey:@"title"];
        [_dict setValue:[rs stringForColumn:@"time"] forKey:@"publish"];
        [_dict setValue:[rs stringForColumn:@"imageurl"] forKey:@"picture"];
        
        [_array addObject:_dict];
    }
    [rs close];
    return _array;

    
}



///////////////////////////////////////////////////////////////////////////
#pragma mark - Table NewsPaperreading
///////////////////////////////////////////////////////////////////////////
- (BOOL) newsPaperReadInsertDataWithColumn:(NSString*)_column paperOid:(NSString*)_oid paperPid:(NSString*)_pid{
    BOOL result = [db executeUpdate:@"INSERT INTO PAPERreading (ncolumn, oid, pid) VALUES (?,?,?)",_column, _oid, _pid];
    return  result;
}
-(BOOL) newsPaperReadDeleteDataWithOid:(NSString*)_oid PaperPid:(NSString *)_pid{
    BOOL result = [db executeUpdate:@"DELETE FROM PAPERreading WHERE oid = ? and pid = ?", _oid , _pid];
    return  result;

}
-(void) newsPaperReadDeleteAllData{
     [db executeUpdate:@"DELETE FROM PAPERreading"];
}
-(BOOL) newsPaperReadIsExistOid:(NSString*)_oid PaperPid:(NSString *)_pid{
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM PAPERreading WHERE oid = ? and pid = ?",_oid,_pid];
    if ([rs next]) {
        return true;
    }
    return false;
}
-(NSMutableArray*) newsPaperReadSelectAll{
    NSMutableArray *_array = [NSMutableArray array];
    NSMutableDictionary *_dict = nil;
    
    //返回全部查询结果
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM PAPERreading"];
    while ([rs next]){
        //NSLog(@" KindKey = %@, Code = %@",[rs stringForColumn:@"KindKey"],[rs stringForColumn:@"Code"]);
        
        _dict = [NSMutableDictionary dictionary];
        [_dict setValue:[rs stringForColumn:@"ncolumn"] forKey:@"newsPaperColumn"];
        [_dict setValue:[rs stringForColumn:@"oid"] forKey:@"oid"];
        [_dict setValue:[rs stringForColumn:@"pid"] forKey:@"pid"];
        
        [_array addObject:_dict];
    }
    [rs close];
    return _array;

}


-(int)newsPaperReadCount{
    
    int count = 0;
    
    //返回全部查询结果
    FMResultSet *rs = [db executeQuery:@"SELECT COUNT(*) FROM PAPERreading"];
    while ([rs next]){
        count = [rs intForColumnIndex:0];
    }
    [rs close];
    return count;
    
}
////////////////////////////////////////////////////////////////////////////////
#pragma mark - Table HTAdvert
////////////////////////////////////////////////////////////////////////////////
//[db executeUpdate:@"CREATE TABLE HTAdvert (kind text, key text, title text, url text, type text, imageurl text)"];

- (void) appAdvertInsertDataWithKindKind:(NSString*)_kind key:(NSString*)_key title:(NSString*)_title url:(NSString*)_url
                                    type:(NSString*)_type identify:(NSString *)_identify imageurl:(NSString*)_imageurl time:(NSString *)_time v:(NSString *)_v{
    [db executeUpdate:@"INSERT INTO HTAdvert304 (kind , key, title , url , type , identify , imageurl , time, v ) VALUES (?,?,?,?,?,?,?,?,?)",_kind, _key, _title, _url, _type,_identify, _imageurl,_time,_v];
    NSLog(@" insert data success ");
}


- (void) appAdvertDeleteAllData{
    [db executeUpdate:@"DELETE FROM HTAdvert304"];
    NSLog(@" --- DELETE HTAdvert304 data success ---");
}



- (NSDictionary*) appAdvertIsExistWithKey:(NSString*)_key {
    
    NSDictionary *temp = nil;
    
    FMResultSet *rs = [db executeQuery:@"SELECT * FROM HTAdvert304 WHERE key = ?", _key];
    
    NSString *key;
    NSString *title;
    NSString *url;
    NSString *type;
    NSString *imageurl;
    NSString *identify;
    NSString *time;
    NSString * v;
    
    while ([rs next]){
        
        key = [rs stringForColumn:@"key"];
        title = [rs stringForColumn:@"title"];
        url = [rs stringForColumn:@"url"];
        type = [rs stringForColumn:@"type"];
        imageurl = [rs stringForColumn:@"imageurl"];
        identify = [rs stringForColumn:@"identify"];
        time = [rs stringForColumn:@"time"];
        v = [rs stringForColumn:@"v"];
        
        
        if (type == nil)
            type = @"";
        if (imageurl == nil)
            imageurl = @"";
        if (identify == nil)
            identify = @"";
        if (time == nil)
            time = @"";
        
        
        @try {
            temp = @{@"key": key, @"title": title, @"url": url, @"type": type, @"identify": identify, @"imageurl": imageurl,@"time":time,@"v":v};
        }
        @catch (NSException *exception) {
            NSLog(@"异常错误是:%@", exception);
        }
        
        
    }
    [rs close];
    
    return temp;
}

@end
